package com.org.util;

import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.FileReader;
import java.io.FileWriter;
import java.io.IOException;
import java.io.InputStream;
import java.io.RandomAccessFile;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Iterator;
import java.util.TimeZone;
import java.util.Timer;
import java.util.TimerTask;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;


public class ExcelUtil {
	private final Timer timer = new Timer();
	private int seconds;
	int j = 1;
	int sumRowCount = 0;

	String months = "";
	String sendError = "";

	public ExcelUtil(int seconds) {
		this.seconds = seconds;
	}

	public ExcelUtil() {
		// TODO Auto-generated constructor stub
	}

	/**
	 * 创建2007版Excel文件
	 * 
	 * @throws FileNotFoundException
	 * @throws IOException
	 */
	private static void creat2007Excel() throws FileNotFoundException,
			IOException {
		// HSSFWorkbook workBook = new HSSFWorkbook();// 创建 一个excel文档对象
		XSSFWorkbook workBook = new XSSFWorkbook();
		XSSFSheet sheet = workBook.createSheet();// 创建一个工作薄对象

		sheet.setColumnWidth(1, 10000);// 设置第二列的宽度为

		XSSFRow row = sheet.createRow(1);// 创建一个行对象

		row.setHeightInPoints(23);// 设置行高23像素

		XSSFCellStyle style = workBook.createCellStyle();// 创建样式对象

		// 设置字体

		XSSFFont font = workBook.createFont();// 创建字体对象

		font.setFontHeightInPoints((short) 15);// 设置字体大小

		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 设置粗体

		font.setFontName("黑体");// 设置为黑体字

		style.setFont(font);// 将字体加入到样式对象

		// 设置对齐方式

		style.setAlignment(HSSFCellStyle.ALIGN_CENTER_SELECTION);// 水平居中

		style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直居中

		// 设置边框

		style.setBorderTop(HSSFCellStyle.BORDER_THICK);// 顶部边框粗线

		style.setTopBorderColor(HSSFColor.RED.index);// 设置为红色

		style.setBorderBottom(HSSFCellStyle.BORDER_DOUBLE);// 底部边框双线

		style.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);// 左边边框

		style.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);// 右边边框

		// 格式化日期

		style.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));

		XSSFCell cell = row.createCell(1);// 创建单元格

		cell.setCellValue(new Date());// 写入当前日期

		cell.setCellStyle(style);// 应用样式对象

		// 文件输出流

		FileOutputStream os = new FileOutputStream("style_2007.xlsx");

		workBook.write(os);// 将文档对象写入文件输出流

		os.close();// 关闭文件输出流
	}

	/**
	 * 创建2003版本的Excel文件
	 */
	private static void creat2003Excel() throws FileNotFoundException,
			IOException {
		HSSFWorkbook workBook = new HSSFWorkbook();// 创建 一个excel文档对象

		HSSFSheet sheet = workBook.createSheet();// 创建一个工作薄对象

		sheet.setColumnWidth(1, 10000);// 设置第二列的宽度为

		HSSFRow row = sheet.createRow(1);// 创建一个行对象

		row.setHeightInPoints(23);// 设置行高23像素

		HSSFCellStyle style = workBook.createCellStyle();// 创建样式对象

		// 设置字体

		HSSFFont font = workBook.createFont();// 创建字体对象

		font.setFontHeightInPoints((short) 15);// 设置字体大小

		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 设置粗体

		font.setFontName("黑体");// 设置为黑体字

		style.setFont(font);// 将字体加入到样式对象

		// 设置对齐方式

		style.setAlignment(HSSFCellStyle.ALIGN_CENTER_SELECTION);// 水平居中

		style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直居中

		// 设置边框

		style.setBorderTop(HSSFCellStyle.BORDER_THICK);// 顶部边框粗线

		style.setTopBorderColor(HSSFColor.RED.index);// 设置为红色

		style.setBorderBottom(HSSFCellStyle.BORDER_DOUBLE);// 底部边框双线

		style.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);// 左边边框

		style.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);// 右边边框

		// 格式化日期

		style.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));

		HSSFCell cell = row.createCell(1);// 创建单元格

		cell.setCellValue(new Date());// 写入当前日期

		cell.setCellStyle(style);// 应用样式对象

		// 文件输出流

		FileOutputStream os = new FileOutputStream("style_2003.xls");

		workBook.write(os);// 将文档对象写入文件输出流

		os.close();// 关闭文件输出流
	}

	/**
	 * 读取excle2007表格内容
	 */
	private void readExcle2007(String filePath) {

		SimpleDateFormat sdf = new SimpleDateFormat("HH:mm:ss:SS");
		TimeZone t = sdf.getTimeZone();
		t.setRawOffset(0);
		sdf.setTimeZone(t);
		Long startTime = System.currentTimeMillis();
		String fileName = "d://model//业绩转移模版.xlsx";
		XSSFWorkbook xwb = null;
		// 检测代码
		try {
			// 构造 XSSFWorkbook 对象，strPath 传入文件路径
			xwb = new XSSFWorkbook(new FileInputStream(fileName));
			// xwb= new XSSFWorkbook(fileName);
			// 读取第一章表格内容
			XSSFSheet sheet = xwb.getSheetAt(0);

			// 定义 row、cell
			XSSFRow row;
			XSSFCell cell;
			String cells;
			String contexts = "";
			for (int i = 1; i < sheet.getPhysicalNumberOfRows(); i++) {
				row = sheet.getRow(i);
				if (row.getCell(0) == null) {
					sumRowCount = i;
					break;
				} else if (row.getCell(0).getCellType() == XSSFCell.CELL_TYPE_BLANK) {
					sumRowCount = i;
					break;
				}

			}
			// 循环输出表格头内容
			row = sheet.getRow(0);
			String head = "";
			for (int j = row.getFirstCellNum() + 1; j < row
					.getPhysicalNumberOfCells(); j++) {
				if (j == 4) {
					continue;
				}
				// 通过 row.getCell(j).toString() 获取单元格内容，
				cells = row.getCell(j).toString();

			}
			head += "</tr>";
			// 循环输出表格中的内容
			String rowsContext = "";
			months = sheet.getRow(1).getCell(0).toString();// 月份
			for (int i = j; i < sumRowCount;) {
				rowsContext = "<tr>";
				row = sheet.getRow(i);
				for (int k = row.getFirstCellNum() + 1; k < row
						.getPhysicalNumberOfCells(); k++) {

					rowsContext += "<td >";
					rowsContext += getCellValue2007(row.getCell(k));
					rowsContext += "</td>";
				}
				rowsContext += "</tr></table>";

				break;
			}
			// end for

		} catch (Exception ex) {

			sendError = "  该用户在第 " + (j + 1) + " 行" + "\n";

			contentToTxt(
					"d://sendMails//"
							+ months.substring(0, months.indexOf("."))
							+ "月份工资条发送失败名单.txt", sendError);

		}
		Long endTime = System.currentTimeMillis();
		
		// 累计读取行号
		j++;
	}

	/**
	 * 读取excle2003表格内容
	 */
	private void readExcle2003(String filePath) {

		SimpleDateFormat sdf = new SimpleDateFormat("HH:mm:ss:SS");

		Long startTime = System.currentTimeMillis();
		HSSFWorkbook xwb = null;
		// 检测代码
		try {
			// 构造 XSSFWorkbook 对象，strPath 传入文件路径
			xwb = new HSSFWorkbook(new FileInputStream(filePath));
			// xwb= new XSSFWorkbook(fileName);
			// 读取第一个工作博
			HSSFSheet sheet = xwb.getSheetAt(0);
			// 定义 row、cell
			HSSFRow row;
			HSSFCell cell;
			String cells;
			// 计算excle总行数
			for (int i = 1; i < sheet.getPhysicalNumberOfRows(); i++) {
				row = sheet.getRow(i);
				if (row.getCell(0) == null) {
					sumRowCount = i;
					break;
				} else if (row.getCell(0).getCellType() == HSSFCell.CELL_TYPE_BLANK) {
					sumRowCount = i;
					break;
				}

			}
			// 循环输出表格头内容
			row = sheet.getRow(0);
			String head = "";
			for (int j = 0; j < row.getPhysicalNumberOfCells(); j++) {
				// 通过 row.getCell(j).toString() 获取单元格内容，
				cells = row.getCell(j).toString();
				head += cells;
			}
			// 循环输出表格中的内容
			String rowsContext = "";
			months = sheet.getRow(1).getCell(0).toString();// 月份
			for (int i = j; i < sumRowCount;) {
				row = sheet.getRow(i);
				for (int k = row.getFirstCellNum() + 1; k < row
						.getPhysicalNumberOfCells(); k++) {
					cell = row.getCell(k);
					if (cell == null) {
						continue;
					}
					rowsContext += getCellValue2003(cell);
				}

				break;
			}
			// end for

		} catch (Exception ex) {

			sendError = "  该用户在第 " + (j + 1) + " 行" + "\n";

			contentToTxt(
					"d://sendMails//"
							+ months.substring(0, months.indexOf("."))
							+ "月份工资条发送失败名单.txt", sendError);

		}
		Long endTime = System.currentTimeMillis();
		// 累计读取行号
		j++;
	}

	private String getCellValue2003(HSSFCell cell) {
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");// 格式化日期字符串
		DecimalFormat nf = new DecimalFormat("0.00");// 格式化数字
		if (cell == null) {
			return "";
		}
		String cellValue = "";
		DecimalFormat df = new DecimalFormat("#");
		switch (cell.getCellType()) {
		case XSSFCell.CELL_TYPE_STRING:
			cellValue = cell.getRichStringCellValue().getString().trim();
			break;
		case XSSFCell.CELL_TYPE_NUMERIC:
			cellValue = df.format(cell.getNumericCellValue()).toString();
			break;
		case XSSFCell.CELL_TYPE_BOOLEAN:
			cellValue = String.valueOf(cell.getBooleanCellValue()).trim();
			break;
		case XSSFCell.CELL_TYPE_FORMULA:
			cellValue = cell.getCellFormula();
			break;
		default:
			cellValue = "";
		}
		return cellValue;
	}

	/**
	 * 取得excle单元格中的值
	 * 
	 * @param cell
	 * @return
	 */
	private String getCellValue2007(XSSFCell cell) {
		if (cell == null) {
			return "";
		}
		String cellValue = "";
		DecimalFormat df = new DecimalFormat("#");
		switch (cell.getCellType()) {
		case XSSFCell.CELL_TYPE_STRING:
			cellValue = cell.getRichStringCellValue().getString().trim();
			break;
		case XSSFCell.CELL_TYPE_NUMERIC:
			cellValue = df.format(cell.getNumericCellValue()).toString();
			break;
		case XSSFCell.CELL_TYPE_BOOLEAN:
			cellValue = String.valueOf(cell.getBooleanCellValue()).trim();
			break;
		case XSSFCell.CELL_TYPE_FORMULA:
			cellValue = cell.getCellFormula();
			break;
		default:
			cellValue = "";
		}
		return cellValue;
	}

	/**
	 * 创建文件
	 * 
	 * @param fileName
	 * @return
	 */
	public static boolean createFile(File fileName) throws Exception {
		boolean flag = false;
		try {
			if (!fileName.exists()) {
				fileName.createNewFile();
				flag = true;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return true;
	}

	public static boolean writeTxtFile(String content, File fileName)
			throws Exception {
		RandomAccessFile mm = null;
		boolean flag = false;
		FileOutputStream o = null;
		try {
			o = new FileOutputStream(fileName);
			o.write(content.getBytes("GBK"));
			o.close();
			// mm=new RandomAccessFile(fileName,"rw");
			// mm.writeBytes(content);
			flag = true;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		} finally {
			if (mm != null) {
				mm.close();
			}
		}
		return flag;
	}

	/**
	 * 记录错误的数据
	 * 
	 * @param filePath
	 * @param content
	 */
	public static void contentToTxt(String filePath, String content) {
		String str = new String(); // 原有txt内容
		String s1 = new String();// 内容更新
		try {
			File f = new File(filePath);
			if (f.exists()) {
				System.out.print("文件存在");
			} else {
				System.out.print("文件不存在");
				f.createNewFile();// 不存在则创建
			}
			BufferedReader input = new BufferedReader(new FileReader(f));

			while ((str = input.readLine()) != null) {
				s1 += str + "\n";
			}
			input.close();
			s1 += content;

			BufferedWriter output = new BufferedWriter(new FileWriter(f));
			output.write(s1);
			output.close();
		} catch (Exception e) {
			e.printStackTrace();

		}
	}

	public static void main(String[] args) {

		// new ExcelUtil().readExcle("d://model//2.xls");
		readXml("d://model//2.xls");
	}

	/**
	 * 判断读取的excle是那个版本，然后读取
	 * 
	 * @param filePath
	 */
	public void readExcle(String filePath) {
		if (filePath.endsWith("xlsx")) {// excle2007
			readExcle2007(filePath);
		} else {
			readExcle2003(filePath);
		}

	}

	public static void readXml(String fileName) {
		boolean isE2007 = false; // 判断是否是excel2007格式
		if (fileName.endsWith("xlsx")) {
			isE2007 = true;
		}
		String cellValue = "";
		DecimalFormat df = new DecimalFormat("#");
		try {
			InputStream input = new FileInputStream(fileName); // 建立输入流
			Workbook wb = null;
			// 根据文件格式(2003或者2007)来初始化
			if (isE2007)
				wb = new XSSFWorkbook(input);
			else
				wb = new HSSFWorkbook(input);
			Sheet sheet = wb.getSheetAt(0); // 获得第一个表单
			Iterator<Row> rows = sheet.rowIterator(); // 获得第一个表单的迭代器
			while (rows.hasNext()) {
				Row row = rows.next(); // 获得行数据
				Iterator<Cell> cells = row.cellIterator(); // 获得第一行的迭代器
				while (cells.hasNext()) {
					Cell cell = cells.next();
					switch (cell.getCellType()) { // 根据cell中的类型来输出数据
					case XSSFCell.CELL_TYPE_STRING:
						cellValue = cell.getRichStringCellValue().getString()
								.trim();
						break;
					case XSSFCell.CELL_TYPE_NUMERIC:
						cellValue = df.format(cell.getNumericCellValue())
								.toString();
						break;
					case XSSFCell.CELL_TYPE_BOOLEAN:
						cellValue = String.valueOf(cell.getBooleanCellValue())
								.trim();
						break;
					case XSSFCell.CELL_TYPE_FORMULA:
						cellValue = cell.getCellFormula();
						break;
					default:
						cellValue = "***";
					}
				}

			}
		} catch (IOException ex) {
			ex.printStackTrace();
		}
	}
}